<?php
/*	
 *  get_user_data.php *
 *  ******************
 *
 *  Created by Louis MAYAUD on 27/01/2013.
 *
 *  DESCRIPTION:
 *		PHP script called to retrieve data from MIMIC-2 server.
 *	
 *	ARGUMENTS:
 *		- Subject_id: patient's ID
 *		- itemid: values from chartevents to display 
 *
 *	RETURNS:
 *		- Json string with values formated for Google visualisation API	
 *
 *	VERSIONNING:
 *		- 27/01/2013 : First released, NHS hack days, Oxford, UK
 */

ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);

// Get variables passed in php's URL
$subject_id=$_GET["subject_id"];
$itemid=$_GET["itemid"];

// Compute Query
$query = "with charts as (
SELECT TO_CHAR(realtime,'YYYY,MM,DD,HH24,MI,SS') timestr,
      TO_CHAR(realtime,'YYYY,MM,DD') timestrcmp,
      extract(year from realtime) y,
      extract(month from realtime) m,
      extract(day from realtime) d,
      extract(hour from realtime) h,
      value1num 
FROM mimic2v26.chartevents c
WHERE c.subject_id=".$subject_id." and c.itemid=".$itemid."
 order by realtime ASC
)
,procs as(
SELECT TO_CHAR(proc_dt,'YYYY,MM,DD') timestrcmp,
      extract(year from proc_dt) y,
      extract(month from proc_dt) m,
      extract(day from proc_dt) d,
      d.description , d.label
FROM mimic2v26.procedureevents p,
mimic2v26.d_codeditems d
WHERE p.subject_id=".$subject_id." 
and p.itemid = d.itemid
and type = 'PROCEDURE'


)

select 
       min( c.timestr ) KEEP (DENSE_RANK FIRST ORDER BY c.timestr) as value,
       min( value1num ) KEEP (DENSE_RANK FIRST ORDER BY c.timestr) as value,
       description
from charts c,
     procs p 
where c.timestrcmp =  p.timestrcmp
group by c.timestrcmp, description  
--order by timestr

UNION

select timestr, value1num, null
from charts c

";

// connect to Database
// This potentially is the time consuming bit. Ideally, you'd like to use oci_connect with the adequate drivers
// Another option is to use PDO instead of OCI.
// the choice will depend on which driver are installed and compatible on both server and client side

// Define connection parameters
$driverName = 'Actual Oracle';
$MimicAdress = 'localhost';
$MimicPort = '1521';
$MimicDB = 'mimic2';
$MimicUser = 'mayaudlo';
xxxxPASSWORD_HERExxx // This line will force crash
$MimicPwd = 'xxxxPASSWORD_HERExxx';
$conn = odbc_connect("Driver={".$driverName."};Server=".$MimicAdress.";Port=".$MimicPort.";Database=".$MimicDB."",$MimicUser,$MimicPwd); 

// Run query
$result = odbc_exec($conn, $query) or die ("Error Execute [".$query."]");
 

// Convert data to Json string
$str = '';    // use oci_fetch_row or dbo_fetch_row  
for($i=1;$row=odbc_fetch_row($result,$i);$i++) {

  // Get formated time string
  $charttime = odbc_result($result, 1);
  // Get value
  $value = odbc_result($result, 2);
  // Get potential procedure code
  $proc = odbc_result($result, 3);
  // If string is value don't do anything  
  if( !is_null($value) ) {
	  // convert to Json format
  	  $str = $str."{\"c\":[{\"v\":\"Date(".$charttime.")\",\"f\":null},{\"v\":".$value.",\"f\":null},{\"v\":\"".$proc."\",\"f\":null},{\"v\":null,\"f\":null}]},";
  }
}

odbc_close($conn);  

// Json HEADER
$jsonheader = "
      {
  \"cols\": [
        {\"id\":\"\",\"label\":\"Date\",\"pattern\":\"\",\"type\":\"datetime\"},
        {\"id\":\"\",\"label\":\"Value\",\"pattern\":\"\",\"type\":\"number\"},
        {\"id\":\"\",\"label\":\"Event\",\"pattern\":\"\",\"type\":\"string\"},
        {\"id\":\"\",\"label\":\"text1\",\"pattern\":\"\",\"type\":\"string\"}
      ],
  \"rows\": [
 ";
 
 $jsonfooter = "]
}
";

// Final Json string
$json = $jsonheader.substr($str, 0, -1).$jsonfooter;

// Return formated data
echo $json;
 
?>
